<?php

namespace app\index\model;

use think\Db;

/**
 * @author xjy
 */
class StoresalesModel
{

    //销售订单列表
    static public function getList($param, $titles = null, $function = null)
    {
        set_time_limit(0);
        $where=[];
        $db = Db::table('sap_order')
                ->alias('a')->join('sap_order_detail d','a.id = d.order_id','left');
        if (!empty($param['sku'])) {
            $where['sku']=$param['sku'];
        }
        if (!empty($param['order_sn_sap'])) {
            $where['order_sn_sap']=$param['order_sn_sap'];
        }
        if (!empty($param['shop'])) {
            $where['shop']=$param['shop'];
        }
        if (!empty($param['type']) && $param['type']=='absolute') {
            $where['status']=2;
        }else{
            $where['status']=['in',[0,1]];
        }
        if (!empty($param['ship_time'])) {
            $ship_time=date('Y-m-d',strtotime($param['ship_time']));
            $ship_time_end=date('Y-m-d',strtotime("+1 day",strtotime($param['ship_time'])));
            $where['ship_time']=['between',[$ship_time." 00:00:00",$ship_time_end." 00:00:00"]];
        }
        $fields='d.id,shop,order_sn_sap,order_time,country,seller,sku,order_quantity,order_price,d.money,currency,rmbamount,exchange_rate,ship_warehouse,ship_time,abnormalResult';
        $db->field($fields);
        $db->where($where);
        $db->order('d.id','desc');
        return \app\common\service\ToolService::downOrReturn($db, $param, $titles,$function);
    }
    /**
     * //sap订单统计
     * 
     * 
     * ****/
    static public function saleReport($param=[])
    {
        $where=[];$list=[];$condition=[];
        $db = Db::table('sap_order')->alias('a')->join('sap_order_detail d','a.id = d.order_id','left');
        $seven_day=clone $db;
        $fourteen_day=clone $db;
        $twentyone_day=clone $db;
        $year_day=clone $db;
        $twentyeight_day=clone $db;
        
        $group="concat(shop,'_',sku)";

        $result=$db->where($where)->group($group)->column("sum(d.rmbamount) total_money,sum(order_quantity) saleamount,shop,sku,country,ship_warehouse_id",$group);  //总销量
        $condition=Db::raw("DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=date(order_time)");
        $quantity7=$seven_day->where($condition)->group($group)->column("sum(order_quantity)/7 7day_average",$group);  //7天均量
    
        $condition=Db::raw("DATE_SUB(CURDATE(),INTERVAL 14 DAY)<=date(order_time)");
        $quantity14=$fourteen_day->where($condition)->group($group)->column('sum(order_quantity)/14 14day_average',$group);//14天均量
        
        $condition=Db::raw("DATE_SUB(CURDATE(),INTERVAL 21 DAY)<=date(order_time)");
        $quantity21=$twentyone_day->where($condition)->group($group)->column('sum(order_quantity)/21 21day_average',$group);//21天均量
        
        $condition=Db::raw("DATE_SUB(CURDATE(),INTERVAL 28 DAY)<=date(order_time)");
        $quantity28=$twentyeight_day->where($condition)->group($group)->column('sum(order_quantity)/28 28day_average',$group);//28天均量
        
        $year=date('Y-01-01');$newyear=date('Y-01-01',strtotime("+1 year"));
        $condition=Db::raw("order_time>='$year' and order_time<'$newyear'");
      //  getinStockPrice($where=[]);
        $warehouse=\app\common\ConfigBase::getinStockPrice();//获取单价
        $year_saleamount=$year_day->where($condition)->group($group)->column("sum(order_quantity) year_saleamount",$group);//年总销量（自然年）
        if($result){
            foreach($result as $key=>$value){
                $one=[];
                $skuInfo=ProduceModel::getSkuCycle(['SKU'=>$value['sku']]);
                $price=1;
                $one['sku_state']=isset($skuInfo[$value['sku']]['sales_state_name'])? $skuInfo[$value['sku']]['sales_state_name']:'';
                $one['cycle_stage']=isset($skuInfo[$value['sku']]['cycle_stage_name'])? $skuInfo[$value['sku']]['cycle_stage_name']:'';
                $one['cname']=isset($skuInfo[$value['sku']]['cname'])? $skuInfo[$value['sku']]['cname']:'';
                $one['7day_average']=isset($quantity7[$key])? $quantity7[$key]:0;
                $one['14day_average']=isset($quantity14[$key])? $quantity14[$key]:0;
                $one['21day_average']=isset($quantity21[$key])? $quantity21[$key]:0;
                $one['28day_average']=isset($quantity28[$key])? $quantity28[$key]:0;
                $one['year_saleamount']=isset($year_saleamount[$key])? $year_saleamount[$key]:0;
                $one['daily_average']= $one['7day_average']*0.5+$one['14day_average']*0.3+$one['28day_average']*0.2;
                $one['sku']=$value['sku'];
                $one['shop']=$value['shop'];
                $one['country']=$value['country'];
                $one['total_money']=$value['total_money'];
                $one['saleamount']=$value['saleamount'];
                $one['census_time']=date('Y-m-d H:i:s');
                if($one['sku']){
                  //  $one['price']=isset($warehouse[$value['ship_warehouse_id'].'_'.$value['sku']]) ? $warehouse[$value['ship_warehouse_id'].'_'.$value['sku']]['wam'] : 0;
                    $list[]=$one;
                }
            }   
        }
        if($list){
            \app\common\model\ToolModel::dbInsertOrUpdate('store_skusale_report', $list);
        }
    }
}
